package parser;

import java.util.*;

import javax.swing.table.DefaultTableModel;

import net.sf.jsqlparser.schema.*;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.arithmetic.*;
import net.sf.jsqlparser.expression.operators.conditional.*;
import net.sf.jsqlparser.expression.operators.relational.*;


public class SelectParser implements SelectVisitor , SelectItemVisitor , ColumnReferenceVisitor
{
	private ArrayList<String> tables;
	private ArrayList<FromItem> fromItems;
	private DefaultTableModel model;
	private String databaseName;
	ArrayList<String> columnList;
	ArrayList<Function> functionList;
	ArrayList<String> functionAlias;
	ArrayList<String> columnAlias;
	List<DefaultTableModel> modelList;
	List<String> selectedColumnList;
	int referenceType;
	boolean isOrderAsc;
	public SelectParser(String databaseName)
	{
		this.databaseName = databaseName;
	}
	public ArrayList<String> getTableList(Select select) 
	{
		tables = new ArrayList<String>();
		select.getSelectBody().accept(this);
		return tables;
	}
	
	public ArrayList<FromItem> getFromItemsList(Select select)
	{
		fromItems = new ArrayList<FromItem>();
		select.getSelectBody().accept(this);
		return fromItems;
	}

	public DefaultTableModel getModel(Select select)
	{
		model = new DefaultTableModel();
		select.getSelectBody().accept(this);
		return model;
	}
	public DefaultTableModel getModel(SubSelect select)
	{
		model = new DefaultTableModel();
		select.getSelectBody().accept(this);
		return model;
	}
	
	public static String getTable(String value)
	{
		int index = value.indexOf('.');
		if (index!=-1) return value.substring(0,index);
		else return null;
	}
	
	public static String getColumn(String value)
	{
		int index = value.indexOf('.');
		return value.substring(index+1);
	}
	
	public static boolean compareColumn(String modelColumn,String columnName)
	{
		String columnTable = getTable(columnName);
		if (columnTable==null)
		{
			String column = getColumn(modelColumn);
			return (column.equalsIgnoreCase(columnName));
		}
		else
		{
			return (modelColumn.equalsIgnoreCase(columnName));
		}
	}
	
	public static int compareValue(String value1,String value2)
	{
		try
		{
			int num1 = Integer.parseInt(value1);
			int num2 = Integer.parseInt(value2);
			if (num1>num2) return 1;
			else if (num1==num2) return 0;
			else return -1;
		}
		catch (NumberFormatException nfe)
		{
			if (value1==null && value2!=null) return -1;
			if (value1!=null && value2==null) return 1;
			if (value1==null && value2==null) return 0;
			else return value1.compareTo(value2);
		}
	}
	public void visit(PlainSelect plainSelect) 
	{
		FromItemParser fromItemParser = new FromItemParser(databaseName);
		model = fromItemParser.getTableModel(plainSelect.getFromItem());
		DefaultTableModel tempModel;
		List<Join> joinList = plainSelect.getJoins();
		if (joinList!=null) for (int i=0;i<joinList.size();i++)
		{
			Join join = joinList.get(i);
			FromItemParser parser = new FromItemParser(databaseName);
			DefaultTableModel model2 = parser.getTableModel(join.getRightItem());
			OnExpressionParser onParser = new OnExpressionParser();
			String joinMode;
			if (join.isFull()) joinMode="Full";
			else if (join.isInner()) joinMode="Inner";
			else if (join.isLeft()) joinMode="Left";
			else if (join.isNatural()) joinMode="Natural";
			else if (join.isOuter()) joinMode="Outer";
			else if (join.isRight()) joinMode="Right";
			else joinMode="Simple";
			if (join.getOnExpression()!=null) model = onParser.GetModel(join.getOnExpression(), model, model2, joinMode);
			else 
			{
				tempModel = new DefaultTableModel(); 
				for (int j=0;j<model.getColumnCount();j++) tempModel.addColumn(model.getColumnName(j));
				for (int j=0;j<model2.getColumnCount();j++) tempModel.addColumn(model2.getColumnName(j));
				
				for (int j=0;j<model.getRowCount();j++)
				{
					for (int k=0;k<model2.getRowCount();k++)
					{
						List<String> values = new ArrayList<String>();
						for (int p=0;p<model.getColumnCount();p++) values.add(model.getValueAt(j, p).toString());
						for (int p=0;p<model2.getColumnCount();p++) values.add(model2.getValueAt(k, p).toString());
						tempModel.addRow(values.toArray()); 
					}
				}
				model = tempModel;
				
			}
		}

		if (plainSelect.getWhere()!=null)
		{
			WhereExpressionParser whereParser = new WhereExpressionParser();
			List<Integer> rowList = whereParser.getRow(plainSelect.getWhere(), model); 
			tempModel = new DefaultTableModel();
			for (int i=0;i<model.getColumnCount();i++) tempModel.addColumn(model.getColumnName(i));
			for (int i=0;i<rowList.size();i++)
			{
				List<String> values = new ArrayList<String>();
				for (int j=0;j<tempModel.getColumnCount();j++) values.add(model.getValueAt(rowList.get(i), j).toString());
				tempModel.addRow(values.toArray());
			}
			model=tempModel;
		}
		selectedColumnList= new ArrayList<String>();
		List<ColumnReference> columnReferences = plainSelect.getGroupByColumnReferences();
		if (columnReferences!=null) 
		{
			modelList = new ArrayList<DefaultTableModel>();
			modelList.add(model);
			referenceType = 0;
			for (int i=0;i<columnReferences.size();i++)
			{
				columnReferences.get(i).accept(this);
			}
			
			
			columnList = new ArrayList<String> ();
			functionAlias = new ArrayList<String> ();
			functionList = new ArrayList<Function>();
			columnAlias = new ArrayList<String> ();
			List<SelectItem> list = plainSelect.getSelectItems();
			for(int i=0;i<list.size();++i) list.get(i).accept(this);
			
			tempModel = new DefaultTableModel();
			for (int j=0;j<columnList.size();j++) 
			{
				int t = -1;
				for (int i=0;i<modelList.get(0).getColumnCount();i++) 
					if (compareColumn(modelList.get(0).getColumnName(i),columnList.get(j))) t = i;
				if (t!=-1) 
				{
					tempModel.addColumn(modelList.get(0).getColumnName(t));
				}
			}
			for (int j=0;j<functionList.size();j++)
			{
				if (functionAlias.get(j)!=null) tempModel.addColumn(functionAlias.get(j));
				else tempModel.addColumn(functionList.get(j).toString());
			}
			List<String> functionResult = new ArrayList<String>();
			ExpressionParser parser = new ExpressionParser();
			for (int k=0;k<modelList.size();k++)
			{
				for (int j=0;j<functionList.size();j++)
				{
					functionResult.add(WhereExpressionParser.doFunction(functionList.get(j), modelList.get(k)));
				}
			}
			
			for (int k=0;k<modelList.size();k++)
			{
				for (int i=0;i<modelList.get(k).getRowCount();i++)
				{
					List tempList = new ArrayList<String>();
					for (int j=0;j<tempModel.getColumnCount()-(functionResult.size()/modelList.size());j++)
					{
						tempList.add(modelList.get(k).getValueAt(i, modelList.get(k).findColumn(tempModel.getColumnName(j))));
					}
					for (int j=functionResult.size()/modelList.size()*k;j<functionResult.size()/modelList.size()*(k+1);j++) tempList.add(functionResult.get(j));
					tempModel.addRow(tempList.toArray());
				}
				if (tempModel.getColumnCount()==(functionResult.size()/modelList.size())) 
					for (int i=tempModel.getRowCount()-1;i>k;i--) tempModel.removeRow(i);
				else if (selectedColumnList.size()==tempModel.getColumnCount()-functionResult.size()/modelList.size())
				{
					boolean t= true;
					for (int i=0;i<selectedColumnList.size();i++) 
						if (!selectedColumnList.get(i).equals(tempModel.getColumnName(i))) t=false;
						if (t) for (int j=tempModel.getRowCount()-1;j>k;j--) tempModel.removeRow(j);
				}
			}
			model = tempModel;

			tempModel = new DefaultTableModel();
			for (int i=0;i<model.getColumnCount();i++) 
			{
				if (i<columnAlias.size())
				{
					if (columnAlias.get(i)!=null) tempModel.addColumn(columnAlias.get(i));
					else tempModel.addColumn(getColumn(model.getColumnName(i)));
				}
				else 
				tempModel.addColumn(getColumn(model.getColumnName(i)));
			}
			for (int i=0;i<model.getRowCount();i++)
			{
				List<String> values = new ArrayList<String>();
				for (int j=0;j<tempModel.getColumnCount();j++) 
					if (model.getValueAt(i, j)!=null) values.add(model.getValueAt(i, j).toString());
					else values.add(null);
				tempModel.addRow(values.toArray());
				
			}

			model = tempModel;
			
			if (plainSelect.getHaving()!=null) 
			{
				
				WhereExpressionParser whereParser = new WhereExpressionParser();
				List<Integer> rowList = whereParser.getRow(plainSelect.getHaving(), model); 
				tempModel = new DefaultTableModel();
				for (int i=0;i<model.getColumnCount();i++) tempModel.addColumn(model.getColumnName(i));
				for (int i=0;i<rowList.size();i++)
				{
					List<String> values = new ArrayList<String>();
					for (int j=0;j<tempModel.getColumnCount();j++) values.add(model.getValueAt(rowList.get(i), j).toString());
					tempModel.addRow(values.toArray());
				}
				model=tempModel;
			}
			
			List<OrderByElement> orderByList= plainSelect.getOrderByElements();
			referenceType=1;
			if (orderByList!=null) for (int i=0;i<orderByList.size();i++) 
			{
				isOrderAsc = orderByList.get(i).isAsc();
				orderByList.get(i).getColumnReference().accept(this);
			}
		}
		else 
		{
			if (plainSelect.getHaving()!=null) 
			{
				WhereExpressionParser whereParser = new WhereExpressionParser();
				List<Integer> rowList = whereParser.getRow(plainSelect.getHaving(), model); 
				tempModel = new DefaultTableModel();
				for (int i=0;i<model.getColumnCount();i++) tempModel.addColumn(model.getColumnName(i));
				for (int i=0;i<rowList.size();i++)
				{
					List<String> values = new ArrayList<String>();
					for (int j=0;j<tempModel.getColumnCount();j++) values.add(model.getValueAt(rowList.get(i), j).toString());
					tempModel.addRow(values.toArray());
				}
				model=tempModel;
			}
			
			columnList = new ArrayList<String> ();
			functionList = new ArrayList<Function>();
			functionAlias = new ArrayList<String> ();
			columnAlias = new ArrayList<String> ();
			List<SelectItem> list = plainSelect.getSelectItems();
			
			for(int i=0;i<list.size();++i) list.get(i).accept(this);
			
			tempModel = new DefaultTableModel();
			for (int j=0;j<columnList.size();j++) 
			{
				int t = -1;
				for (int i=0;i<model.getColumnCount();i++) 
					if (compareColumn(model.getColumnName(i),columnList.get(j))) t = i;
				if (t!=-1) tempModel.addColumn(model.getColumnName(t));
			}
			
			List<String> functionResult = new ArrayList<String>();
			ExpressionParser parser = new ExpressionParser();
			for (int j=0;j<functionList.size();j++)
			{
				if (functionAlias.get(j)!=null) tempModel.addColumn(functionAlias.get(j));
				else tempModel.addColumn(functionList.get(j).toString());
				functionResult.add(WhereExpressionParser.doFunction(functionList.get(j), model));
			}
			
			for (int i=0;i<model.getRowCount();i++)
			{
				List tempList = new ArrayList<String>();
				for (int j=0;j<tempModel.getColumnCount()-functionResult.size();j++)
				{
					tempList.add(model.getValueAt(i, model.findColumn(tempModel.getColumnName(j))));
				}
				for (int j=0;j<functionResult.size();j++) tempList.add(functionResult.get(j));
				tempModel.addRow(tempList.toArray());
			}
			model = tempModel;
			
			

			tempModel = new DefaultTableModel();
			for (int i=0;i<model.getColumnCount();i++) 
			if (i<columnAlias.size())
			{
				if (columnAlias.get(i)!=null) tempModel.addColumn(columnAlias.get(i));
				else tempModel.addColumn(getColumn(model.getColumnName(i)));
			}
			else tempModel.addColumn(getColumn(model.getColumnName(i)));
			for (int i=0;i<model.getRowCount();i++)
			{
				List<String> values = new ArrayList<String>();
				for (int j=0;j<tempModel.getColumnCount();j++) 
					if (model.getValueAt(i, j)!=null) values.add(model.getValueAt(i, j).toString());
					else values.add(null);
				tempModel.addRow(values.toArray());
			}
			model = tempModel;
			
			if (model.getColumnCount()==functionResult.size()) for (int i=model.getRowCount()-1;i>0;i--) model.removeRow(i);
			
			List<OrderByElement> orderByList= plainSelect.getOrderByElements();
			referenceType=1;
			if (orderByList!=null) for (int i=0;i<orderByList.size();i++) 
			{
				isOrderAsc = orderByList.get(i).isAsc();
				orderByList.get(i).getColumnReference().accept(this);
			}
		}
			
		
		/*plainSelect.getFromItem().accept(this);
		
		if (plainSelect.getJoins() != null) {
			for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext();) {
				Join join = (Join) joinsIt.next();
				
				join.getRightItem().accept(this);
			}
		}*/

	}

	public void visit(Union union) 
	{
		/*for (Iterator iter = union.getPlainSelects().iterator(); iter.hasNext();) {
			PlainSelect plainSelect = (PlainSelect) iter.next();
			visit(plainSelect);
		}*/
	}
	@Override
	public void visit(AllColumns allColumns) 
	{
		columnList.clear();
		for (int i=0;i<model.getColumnCount();i++) columnList.add(model.getColumnName(i));
	}
	@Override
	public void visit(AllTableColumns allTableColumns) 
	{
		columnList.clear();
		for (int i=0;i<model.getColumnCount();i++) columnList.add(model.getColumnName(i));
		
		
	}
	@Override
	public void visit(SelectExpressionItem selectExpressionItem) 
	{
		SelectExpressionParser parser = new SelectExpressionParser();
		String columnName = parser.getColumn(selectExpressionItem);
		if (columnName!=null) 
		{
			columnList.add(parser.getColumn(selectExpressionItem));
			columnAlias.add(selectExpressionItem.getAlias());
		}
		else 
		{
			String s=null;
			Function function = parser.getFunction(selectExpressionItem);
			functionAlias.add(selectExpressionItem.getAlias());
			if (function!=null) functionList.add(function);
		}
	}
	@Override
	public void visit(ColumnIndex columnIndex) 
	{
		
		int index=columnIndex.getIndex();
		if (referenceType==0)
		{
			selectedColumnList.add(modelList.get(0).getColumnName(index));
			List<DefaultTableModel> tempList = new ArrayList<DefaultTableModel>();
			List<String> values;
			for (int i=0;i<modelList.size();i++)
			{
				List<Integer> rowList= new ArrayList<Integer>();
				for (int j=0;j<modelList.get(i).getRowCount();j++) rowList.add(j);
				while (rowList.size()>0)
				{
					DefaultTableModel model = new DefaultTableModel();
					String value1;
					values = new ArrayList<String>();
					for (int j=0;j<modelList.get(i).getColumnCount();j++) model.addColumn(modelList.get(i).getColumnName(j));
					if (modelList.get(i).getValueAt(0, index)!=null) value1=modelList.get(i).getValueAt(rowList.get(0), index).toString();
					else value1=null;
					
					for (int j=0;j<modelList.get(i).getColumnCount();j++) 
						if (modelList.get(i).getValueAt(0, j)!=null) values.add(modelList.get(i).getValueAt(rowList.get(0), j).toString());
						else values.add(null);
					model.addRow(values.toArray());
					for (int k=1;k<rowList.size();k++) 
					{
						String value2;
						if (modelList.get(i).getValueAt(k, index)!=null) value2=modelList.get(i).getValueAt(rowList.get(k), index).toString();
						else value2=null;
						if (value1.equals(value2))
						{
							values = new ArrayList<String>();
							for (int j=0;j<modelList.get(i).getColumnCount();j++) 
								if (modelList.get(i).getValueAt(rowList.get(k), j)!=null) values.add(modelList.get(i).getValueAt(rowList.get(k), j).toString());
								else values.add(null);
							model.addRow(values.toArray());
							rowList.remove(k);
							k--;
						}
					}
					rowList.remove(0);
					tempList.add(model);
				}
			}
			modelList=tempList;
		}
		else if (referenceType==1)
		{
			for (int i=0;i<model.getRowCount()-1;i++)
			{
				for (int j=i+1;j<model.getRowCount();j++)
				{
					String value1,value2;
					if (model.getValueAt(i,index)==null) value1=null;
					else value1= model.getValueAt(i, index).toString();
					if (model.getValueAt(j,index)==null) value2=null;
					else value2= model.getValueAt(j, index).toString();
					if (isOrderAsc)
					{
						if (compareValue(value1,value2)>0) 
						{
							Object temp;
							for (int k=0;k<model.getColumnCount();k++) 
							{
								temp = model.getValueAt(i, index);
								model.setValueAt(model.getValueAt(j, index), i, index);
								model.setValueAt(temp, j, index);
							}
							
						}
					}
					else
					{
						if (compareValue(value1,value2)<0)
						{
							Object temp;
							for (int k=0;k<model.getColumnCount();k++) 
							{
								temp = model.getValueAt(i, index);
								model.setValueAt(model.getValueAt(j, index), i, index);
								model.setValueAt(temp, j, index);
							}
							
						}
					}
				}
			}
		}
	}
	@Override
	public void visit(Column column) 
	{
		int index; 
		
		if (referenceType==0)
		{
			String columnName;
			if (SelectParser.getTable(column.getWholeColumnName())==null) 
				columnName = SelectParser.getTable(modelList.get(0).getColumnName(0))+'.'+column.getWholeColumnName();
			else columnName = column.getWholeColumnName();
			index =  modelList.get(0).findColumn(columnName);
			selectedColumnList.add(columnName);
			List<DefaultTableModel> tempList = new ArrayList<DefaultTableModel>();
			List<String> values;
			for (int i=0;i<modelList.size();i++)
			{
				List<Integer> rowList= new ArrayList<Integer>();
				for (int j=0;j<modelList.get(i).getRowCount();j++) rowList.add(j);
				while (rowList.size()>0)
				{
					DefaultTableModel model = new DefaultTableModel();
					String value1;
					values = new ArrayList<String>();
					for (int j=0;j<modelList.get(i).getColumnCount();j++) model.addColumn(modelList.get(i).getColumnName(j));
					if (modelList.get(i).getValueAt(0, index)!=null) value1=modelList.get(i).getValueAt(rowList.get(0), index).toString();
					else value1=null;
					
					for (int j=0;j<modelList.get(i).getColumnCount();j++) 
						if (modelList.get(i).getValueAt(0, j)!=null) values.add(modelList.get(i).getValueAt(rowList.get(0), j).toString());
						else values.add(null);
					model.addRow(values.toArray());
					for (int k=1;k<rowList.size();k++) 
					{
						String value2;
						if (modelList.get(i).getValueAt(k, index)!=null) value2=modelList.get(i).getValueAt(rowList.get(k), index).toString();
						else value2=null;
						if (value1.equals(value2))
						{
							values = new ArrayList<String>();
							for (int j=0;j<modelList.get(i).getColumnCount();j++) 
								if (modelList.get(i).getValueAt(rowList.get(k), j)!=null) values.add(modelList.get(i).getValueAt(rowList.get(k), j).toString());
								else values.add(null);
							model.addRow(values.toArray());
							rowList.remove(k);
							k--;
						}
					}
					rowList.remove(0);
					tempList.add(model);
				}
			}
			modelList=tempList;
		}
		else if (referenceType==1)
		{
			String columnName = column.getWholeColumnName();
			index =  model.findColumn(columnName);
			for (int i=0;i<model.getRowCount()-1;i++)
			{
				for (int j=i+1;j<model.getRowCount();j++)
				{
					String value1,value2;
					if (model.getValueAt(i,index)==null) value1=null;
					else value1= model.getValueAt(i, index).toString();
					if (model.getValueAt(j,index)==null) value2=null;
					else value2= model.getValueAt(j, index).toString();
					if (isOrderAsc)
					{
						if (compareValue(value1,value2)>0)
						{
							Object temp;
							for (int k=0;k<model.getColumnCount();k++) 
							{
								temp = model.getValueAt(i, k);
								model.setValueAt(model.getValueAt(j, k), i, k);
								model.setValueAt(temp, j, k);
							}
							
						}
					}
					else
					{
						if (compareValue(value1,value2)<0)
						{
							Object temp;
							for (int k=0;k<model.getColumnCount();k++) 
							{
								temp = model.getValueAt(i, k);
								model.setValueAt(model.getValueAt(j, k), i, k);
								model.setValueAt(temp, j, k);
							}
							
						}
					}
				}
			}
		}
	}
	


}
